package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 适用于时代的Excel模板
* Title: 
* Description: 
* @author landa
* @date 2017年4月18日 下午5:40:05
 */
public class CopyWriteTwo {
	
	/**
	 * 同一个Excel复制Sheet[测试过程中,最多只增加了4个Sheet,会覆盖]
	 * @param file
	 * @throws Exception
	 */
	private static void copyFromAtoA1(String file ) throws Exception  {
		Workbook wb = null;
		if (file.endsWith(".xlsx")) {//EXCEL2007
			wb = new XSSFWorkbook(new FileInputStream(new File(file)));
		}else if(file.endsWith(".xls")){//EXCEL97-2003
			wb = new HSSFWorkbook(new FileInputStream(new File(file)));
		}else{
			throw new Exception("");
		}
		
		if(wb.getNumberOfSheets() > 1){
			wb.removeSheetAt(1);
		}
		
		//单元格合并
		CellRangeAddress region = null;
		Sheet sheet1 = wb.getSheetAt(0);
		Sheet sheet2 = wb.createSheet(sheet1.getSheetName() + "a_副本");
		
		for (int i = 0; i < sheet1.getNumMergedRegions(); i++) {
			region = sheet1.getMergedRegion(i);
			if ((region.getFirstColumn() >= sheet1.getFirstRowNum())
					&& (region.getLastRow() <= sheet1.getLastRowNum())) {
				sheet2.addMergedRegion(region);
			}
		}

		//复制内容
		Row rowFrom = null;
		Row rowTo = null;
		Cell cellFrom = null;
		Cell cellTo = null;
		for (int i = sheet1.getFirstRowNum(); i < sheet1.getLastRowNum(); i++) {
			rowFrom = sheet1.getRow(i);
			if (null == rowFrom){
				continue;
			}
			
			rowTo = sheet2.createRow(i);
			rowTo.setHeight(rowFrom.getHeight());
			for (int j = 0; j < rowFrom.getLastCellNum(); j++) {
				sheet2.setColumnWidth(j, sheet1.getColumnWidth(j));
				if(null != sheet1.getColumnStyle(j)){
					sheet2.setDefaultColumnStyle(j, sheet1.getColumnStyle(j));
				}
				
				cellFrom = rowFrom.getCell(j);
				if (null == cellFrom){
					continue;
				}
				
				cellTo = rowTo.createCell(j);
				cellTo.setCellStyle(cellFrom.getCellStyle());
				/**
				 * poi按照一个源单元格设置目标单元格格式，如果两个单元格不在同一个workbook，
					要用
					HSSFCellStyle下的cloneStyleFrom()方法
				 */
//				cellTo.getCellStyle().cloneStyleFrom(cellFrom.getCellStyle());//
				
				cellTo.setCellType(cellFrom.getCellType());
				
				if(Cell.CELL_TYPE_STRING == cellFrom.getCellType()){
					cellTo.setCellValue(cellFrom.getStringCellValue());
				}else if(Cell.CELL_TYPE_NUMERIC == cellFrom.getCellType()){
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			}
		}

		sheet2.setDisplayGridlines(true);//
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut); 
        fileOut.close(); 
        
        System.out.println(file + " 复制sheet成功!");
	}
	
	/**
	 * 同一个Excel复制Sheet
	 * @param file
	 * @throws Exception
	 */
	 public static void copyFromAtoA2(String file ) throws Exception  {
		Workbook wb = null;
		if (file.endsWith(".xlsx")) {//EXCEL2007
			wb = new XSSFWorkbook(new FileInputStream(new File(file)));
		}else if(file.endsWith(".xls")){//EXCEL97-2003
			wb = new HSSFWorkbook(new FileInputStream(new File(file)));
		}else{
			throw new Exception("");
		}
		
		Sheet sheet1 = wb.cloneSheet(0);
//		 workbook.setSheetName(sheet的索引, "名字");
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut); 
        fileOut.close(); 
        
        System.out.println(file + " 复制sheet成功!");
	}
	
	/**
	 * 不同Excel复制Sheet
	 * 问题:颜色,居中的格式弄丢了
	 * @param file
	 * @throws Exception
	 */
	  static void copyFromAtoB(String file,String tofile) throws Exception  {
		Workbook wb = null;
		if (file.endsWith(".xlsx")) {//EXCEL2007
			wb = new XSSFWorkbook(new FileInputStream(new File(file)));
		}else if(file.endsWith(".xls")){//EXCEL97-2003
			wb = new HSSFWorkbook(new FileInputStream(new File(file)));
		}else{
			throw new Exception("");
		}
		
		if(wb.getNumberOfSheets() > 1){
			wb.removeSheetAt(1);
		}
		
		//单元格合并
		CellRangeAddress region = null;
		Sheet sheet1 = wb.getSheetAt(0);
		
		FileInputStream fis = new FileInputStream(tofile);
		// 去读取Excel
		XSSFWorkbook wb2 = new XSSFWorkbook(fis);
		Sheet sheet2 = wb2.createSheet(sheet1.getSheetName() + "_123456789副本");
		for (int i = 0; i < sheet1.getNumMergedRegions(); i++) {
			region = sheet1.getMergedRegion(i);
			if ((region.getFirstColumn() >= sheet1.getFirstRowNum())
					&& (region.getLastRow() <= sheet1.getLastRowNum())) {
				sheet2.addMergedRegion(region);
			}
		}

		//复制内容
		Row rowFrom = null;
		Row rowTo = null;
		Cell cellFrom = null;
		Cell cellTo = null;
		for (int i = sheet1.getFirstRowNum(); i < sheet1.getLastRowNum(); i++) {
			rowFrom = sheet1.getRow(i);
			if (null == rowFrom){
				continue;
			}
			
			rowTo = sheet2.createRow(i);
			rowTo.setHeight(rowFrom.getHeight());
			for (int j = 0; j < rowFrom.getLastCellNum(); j++) {
				sheet2.setColumnWidth(j, sheet1.getColumnWidth(j));
				if(null != sheet1.getColumnStyle(j)){
					sheet2.setDefaultColumnStyle(j, sheet1.getColumnStyle(j));
				}
				
				cellFrom = rowFrom.getCell(j);
				if (null == cellFrom){
					continue;
				}
				
				cellTo = rowTo.createCell(j);
//				cellTo.setCellStyle(cellFrom.getCellStyle());
				/**
				 * poi按照一个源单元格设置目标单元格格式，如果两个单元格不在同一个workbook，
					要用
					HSSFCellStyle下的cloneStyleFrom()方法
				 */
				cellTo.getCellStyle().cloneStyleFrom(cellFrom.getCellStyle());//
				
				cellTo.setCellType(cellFrom.getCellType());
				
				if(Cell.CELL_TYPE_STRING == cellFrom.getCellType()){
					cellTo.setCellValue(cellFrom.getStringCellValue());
				}else if(Cell.CELL_TYPE_NUMERIC == cellFrom.getCellType()){
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			}
		}

		sheet2.setDisplayGridlines(true);//
		FileOutputStream fileOut = new FileOutputStream(tofile);
		wb2.write(fileOut); 
        fileOut.close(); 
        
        System.out.println(file + " 复制sheet成功!");
	}
	
	public static void main(String[] args) throws Exception {
		File dir = new File("");
		String path  = dir.getAbsolutePath();
//		POIExcelUtil.read_Excel(path+"\\src\\read\\template.xlsx", 1);
//		copyFromAtoB(path+"\\src\\read\\template.xlsx",path+"\\src\\read\\template4.xlsx" );
		//copyFromAtoA2(path+"\\src\\read\\template.xlsx"  );
		String source = path+"\\src\\read\\templateA.xlsx";
		String dest = path+"\\src\\read\\template5.xlsx" ;
		System.out.println("拷贝的模板:"+source);
		System.out.println("生成后的数据模板:"+dest);
		copyAndWrite( source,dest);//模板-->需生成的
		copyFromAtoA2(dest);//需生成的--创建多个Sheet
		copyFromAtoA2(dest);//需生成的--创建多个Sheet
		copyFromAtoA2(dest);//需生成的--创建多个Sheet
		copyFromAtoA2(dest);//需生成的--创建多个Sheet
		writeData(dest);//往Sheet写数据
	}
	
	
	
	/**
	 * 复制文件AtoB
	 * @param source
	 * @param dest
	 * @throws Exception
	 */
	public static void copyAndWrite(String source,String dest) throws Exception{
		// 复制文件  
        int byteread = 0; // 读取的字节数  
        InputStream in = null;  
        OutputStream out = null;  
  
            in = new FileInputStream(source);  
            File f = new File(dest);
            if(f.exists()){
            	f.delete();
            }
            out = new FileOutputStream(dest);  
            byte[] buffer = new byte[1024];  
  
            while ((byteread = in.read(buffer)) != -1) {  
                out.write(buffer, 0, byteread);  
            }  
           
            if(out!=null){
            	out.close();
            }
            if(in!=null){
            	in.close();
            }
            
	}
	
	
	/**
	 * 往Excel写入数据
	 * @param source
	 * @param dest
	 * @throws Exception
	 */
	public static void writeData(String file) throws Exception{
		StringBuffer t = new StringBuffer("一帆风顺+二龙戏珠+三阳开泰+四平八稳+五福临门+六六大顺+七星报喜+八面威风+九九回一+十全十美+一到工作抛+二到烦恼消+三到忧愁跑+四到心情妙+五到兴致高+六到欢快绕+七到幸福靠+八到收进好+九到步步高+十全十美一到国庆+二目放光+三神俱喜+四肢愉快+五朋六友+七聚八乐+久(九)久兴奋+十分舒爽+十一国庆+欢快飞涨!(推荐的从一到十的祝福语—国庆祝福语↑)用一缕东风+二滴夏雪+三片秋叶+四朵冬雪+做成五颜六色的礼盒+打着七彩八飘的丝带+用九分真诚十分热情装进我最真诚的祝福！+三到忧愁跑+四到心情妙+五到兴致高+六到欢快绕");
		t.append("一帆风顺+二龙戏珠+三阳开泰+四平八稳+五福临门+六六大顺+七星报喜+八面威风+九九回一+十全十美+一到工作抛+二到烦恼消+三到忧愁跑+四到心情妙");
		String[] list = t.toString().split("\\+");
		
		 List<String> list2 = new ArrayList<String>();
		 list2.add("节点1");
		 list2.add("节点2");
		 list2.add("节点3");
		 list2.add("节点4");
		 list2.add("节点5");
		 list2.add("节点6");
		 list2.add("节点7");
		 list2.add("节点8");
		 list2.add("节点9");
		 list2.add("节点10");
		 
		 SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
		 System.out.println(sdf.format(new Date()));
			
			
			FileInputStream fis = new FileInputStream(file);
			
			// 去读取Excel
			XSSFWorkbook wb = new XSSFWorkbook(fis);
			
			Sheet sheet1 = wb.getSheetAt(1);
			
			CellStyle style = wb.createCellStyle();
			XSSFFont font = wb.createFont();
			font.setFontHeight(80);
			
		    style.setTopBorderColor(HSSFColor.BLACK.index);

            style.setBottomBorderColor(HSSFColor.BLACK.index);

            style.setLeftBorderColor(HSSFColor.BLUE.index);

            style.setRightBorderColor(HSSFColor.BLACK.index);
            

            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
             
            font.setFontName("华文幼体");

            font.setFontHeightInPoints((short)15);
            

            font.setColor(HSSFColor.BLACK.index);

            font.setBoldweight(font.BOLDWEIGHT_BOLD);

//	            font.setItalic(true);

//	            font.setStrikeout(true);

//	            font.setUnderline((byte)1);
            
            style.setFont(font);
			
			CellStyle cellStyle = sheet1.getRow(3).getCell(2).getCellStyle();
//			 System.out.println("字体: "+cellStyle.getFontIndex());
//			style.cloneStyleFrom(cellStyle);
			style.setFillBackgroundColor(HSSFColor.BLUE.index);
//			style.setf
			// 写入
			Row rowFrom = null;
			Cell cellFrom = null;
			for (int i = 5; i < 30; i+=2) { //设置为记录数
				rowFrom = sheet1.getRow(i);
//				if (null == rowFrom){
//					continue;
//				}
				rowFrom = sheet1.createRow(i);
				rowFrom.setHeight((short)900);
				
				for (int j = 0; j < list.length; j++) {
//					cellFrom = rowFrom.getCell(j);
					cellFrom = rowFrom.createCell(j);
					cellFrom.setCellValue(list[j]);
					cellFrom.setCellStyle(style);
				}
			}
			
			for (int i = 4; i < 30; i+=2) { //设置为记录数[节点的]
				rowFrom = sheet1.getRow(i);
//				if (null == rowFrom){
//					continue;
//				}
				rowFrom = sheet1.createRow(i);
				rowFrom.setHeight((short)900);
				for(int j = 0;j<35;j++){
					cellFrom = rowFrom.createCell(j);
					cellFrom.setCellStyle(style);
//					cellFrom.setCellValue(j);
				}
				for (int j = 0; j < list2.size(); j++) {
//					cellFrom = rowFrom.getCell(15+j);
					cellFrom = rowFrom.createCell(35+j);
					cellFrom.setCellValue(list2.get(j));
					cellFrom.setCellStyle(style);
				}
			}
			

			FileOutputStream fileOut = new FileOutputStream(file);
			wb.write(fileOut); 
	        fileOut.close(); 
	        System.out.println("写入成功");
	        System.out.println(sdf.format(new Date()));
	}
	
}


/**


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelTest {
	
	private static final String EXL_2003 = "C:\\Users\\Administrator\\Desktop\\sale2003.xls";
	private static final String EXL_2007 = "C:\\Users\\Administrator\\Desktop\\sale2007.xlsx";
	
	public static void main(String[] args){
		try {
			test(EXL_2003);
			test(EXL_2007);
		} catch (Exception e) {
			System.out.println("请检查EXCEL文件及格式!!");
		}
	}

	private static void test(String file) throws Exception  {
		Workbook wb = null;
		if (file.endsWith(".xlsx")) {//EXCEL2007
			wb = new XSSFWorkbook(new FileInputStream(new File(file)));
		}else if(file.endsWith(".xls")){//EXCEL97-2003
			wb = new HSSFWorkbook(new FileInputStream(new File(file)));
		}else{
			throw new Exception("");
		}
		
		if(wb.getNumberOfSheets() > 1){
			wb.removeSheetAt(1);
		}
		
		//单元格合并
		CellRangeAddress region = null;
		Sheet sheet1 = wb.getSheetAt(0);
		Sheet sheet2 = wb.createSheet(sheet1.getSheetName() + "_副本");
		for (int i = 0; i < sheet1.getNumMergedRegions(); i++) {
			region = sheet1.getMergedRegion(i);
			if ((region.getFirstColumn() >= sheet1.getFirstRowNum())
					&& (region.getLastRow() <= sheet1.getLastRowNum())) {
				sheet2.addMergedRegion(region);
			}
		}

		//复制内容
		Row rowFrom = null;
		Row rowTo = null;
		Cell cellFrom = null;
		Cell cellTo = null;
		for (int i = sheet1.getFirstRowNum(); i < sheet1.getLastRowNum(); i++) {
			rowFrom = sheet1.getRow(i);
			if (null == rowFrom){
				continue;
			}
			
			rowTo = sheet2.createRow(i);
			rowTo.setHeight(rowFrom.getHeight());
			for (int j = 0; j < rowFrom.getLastCellNum(); j++) {
				sheet2.setColumnWidth(j, sheet1.getColumnWidth(j));
				if(null != sheet1.getColumnStyle(j)){
					sheet2.setDefaultColumnStyle(j, sheet1.getColumnStyle(j));
				}
				
				cellFrom = rowFrom.getCell(j);
				if (null == cellFrom){
					continue;
				}
				
				cellTo = rowTo.createCell(j);
				cellTo.setCellStyle(cellFrom.getCellStyle());
				cellTo.setCellType(cellFrom.getCellType());
				
				if(Cell.CELL_TYPE_STRING == cellFrom.getCellType()){
					cellTo.setCellValue(cellFrom.getStringCellValue());
				}else if(Cell.CELL_TYPE_NUMERIC == cellFrom.getCellType()){
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			}
		}

		sheet2.setDisplayGridlines(true);//
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut); 
        fileOut.close(); 
        
        System.out.println(file + " 复制sheet成功!");
	}

}
 */
